﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Assessment.Management.SharedComponent.Params.ManagerParam;
using Assessment.Management.SharedComponent.Constants;
using Assessment.Management.SharedComponent.Entities;
using System.Data.SqlClient;

namespace Assessment.Management.DAO
{
    public class SubjectDAO : BaseDaos
    {
        public SubjectDAO()
            : base()
        {
        }
        public void GetListSubjectByTeacherIDAndClassID(SubjectParam param)
        {
            string query = @"Select s.ID, s.SubjectName from Teacher_Class as tc INNER JOIN Subjects as s ON tc.SubjectID = s.ID
                                        where tc.Deleted=@Deleted and tc.TeacherID=@TeacherID and tc.ClassID = @ClassID";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@TeacherID", param.Subject.TeacherID);
            cmd.Parameters.AddWithValue("@ClassID", param.Subject.ClassID);

            try
            {
                param.listSubject = base.ExecuteQueryGetListObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
        public void GetListSubject(SubjectParam param)
        {
            string query = "Select * from Subjects where Deleted=@Deleted";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listSubject = base.ExecuteQueryGetListObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetSubjectToEditByCode(SubjectParam param)
        {
            string query = "Select * from Subjects where Deleted=@Deleted and ID=@ID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@ID", param.Subject.ID);

            try
            {
                param.Subject = base.ExecuteQueryGetObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetListSubjectByDepartmentID(SubjectParam param)
        {

            string query = @"Select * from Subjects where Deleted=@Deleted and DepartmentID=@DepartmentID";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@DepartmentID", param.Subject.DepartmentID);
            try
            {
                param.listSubject = base.ExecuteQueryGetListObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CreateSubject(SubjectParam param)
        {
            string query = @"Insert into Subjects (ID, SubjectName, Credit, CreatedBy, CreatedDTG, Deleted, Version) 
                                        values(@ID, @SubjectName, @Credit, @CreatedBy, @CreatedDTG, @Deleted, @Version)";

            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@ID", param.Subject.ID);
            cmd.Parameters.AddWithValue("@SubjectName", param.Subject.SubjectName);
            cmd.Parameters.AddWithValue("@Credit", param.Subject.Credit);
            cmd.Parameters.AddWithValue("@CreatedBy", param.Subject.CreatedBy);
            cmd.Parameters.AddWithValue("@CreatedDTG", param.Subject.CreatedDTG);
            cmd.Parameters.AddWithValue("@Deleted", param.Subject.Deleted);
            cmd.Parameters.AddWithValue("@Version", param.Subject.Version);

            try
            {
                int count = base.ExecuteNonQuery(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void UpdateSubject(SubjectParam param, SqlCommand sqlCmd = null)
        {
            string query = @"Update Subjects 
                              Set SubjectName        = @SubjectName, 
                                Credit        = @Credit, 
                                Version        = Version+1,
                                UpdatedBy      = @UpdatedBy,
                                UpdatedDTG     = @UpdatedDTG
                            where ID         = @ID and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }

            sqlCmd.CommandText = query;
            sqlCmd.Parameters.Clear();
            sqlCmd.Parameters.AddWithValue("@ID", param.Subject.ID);
            sqlCmd.Parameters.AddWithValue("@SubjectName", param.Subject.SubjectName);
            sqlCmd.Parameters.AddWithValue("@Credit", param.Subject.Credit);
            sqlCmd.Parameters.AddWithValue("@UpdatedBy", param.Subject.UpdatedBy);
            sqlCmd.Parameters.AddWithValue("@UpdatedDTG", param.Subject.UpdatedDTG);
            sqlCmd.Parameters.AddWithValue("@Version", param.Subject.Version);


            try
            {
                int count = base.ExecuteNonQuery(sqlCmd);
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void DeleteSubjects(SubjectParam param, SqlCommand sqlCmd = null)
        {
            var query = @"Update Subjects
                          Set Deleted        = @Deleted,
                              Version        = Version+1,
                              UpdatedBy      = @UpdatedBy,
                              UpdatedDTG     = @UpdatedDTG
                          Where ID = @ID and Version=@Version";

            // if SqlCommand argument is null, it means this function is not in transaction
            bool isUsingTransaction = sqlCmd != null;
            if (sqlCmd == null)
            {
                sqlCmd = base.GetSqlCommand();
            }
            List<Subject> lstDeleteSubject = param.listSubject;
            sqlCmd.CommandText = query;

            try
            {
                foreach (Subject item in lstDeleteSubject)
                {
                    sqlCmd.Parameters.Clear();
                    sqlCmd.Parameters.AddWithValue("@Deleted", item.Deleted);
                    sqlCmd.Parameters.AddWithValue("@Version", item.Version);
                    sqlCmd.Parameters.AddWithValue("@UpdatedBy", item.UpdatedBy);
                    sqlCmd.Parameters.AddWithValue("@UpdatedDTG", item.UpdatedDTG);
                    sqlCmd.Parameters.AddWithValue("@ID", item.ID);
                    int count = base.ExecuteNonQuery(sqlCmd);
                }
            }
            finally
            {
                // If this function is using transaction from biz, do not close connection
                if (!isUsingTransaction)
                {
                    base.CloseConnection(sqlCmd);
                }
            }
        }

        public void SearchSubjects(SubjectParam param)
        {
            string query = @"select ID, SubjectName, Credit from Subjects 
                                where (@SubjectName='' or SubjectName like N'%'+@SubjectName+N'%') 
                                                          and Deleted=@Deleted";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@ID", param.Subject.ID);
            cmd.Parameters.AddWithValue("@SubjectName", param.Subject.SubjectName);
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.listSubject = base.ExecuteQueryGetListObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void CheckExitsData(SubjectParam param)
        {
            string query = @"select count(*) from Subjects where Deleted=@Deleted and ID = @ID";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            cmd.Parameters.AddWithValue("@ID", param.Subject.ID);
            try
            {
                param.Subject.ID = base.ExecuteScalar(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }

        public void GetLastSubjectCode(SubjectParam param)
        {
            //string query = "select top 1 Ma from test2";
            string query = "select top 1 ID from Subjects where Deleted=@Deleted order by CreatedDTG desc";
            var cmd = base.GetSqlCommand();
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@Deleted", SMX.smx_IsNotDeleted);
            try
            {
                param.Subject = base.ExecuteQueryGetObject<Subject>(cmd);
            }
            finally
            {
                base.CloseConnection(cmd);
            }
        }
    }
}
